# -- coding: utf-8 --

from openpyxl import load_workbook
from openpyxl.workbook import Workbook

UP = '上涨'
DOWN = '下跌'


class Participant(object):
    user_id = None
    start_time = None
    end_time = None
    province = None
    city = None

    ds1_trend = None
    ds1_next = None
    ds2_trend = None
    ds2_next = None
    ds3_trend = None
    ds3_next = None

    dl1_trend = None
    dl1_next = None
    dl2_trend = None
    dl2_next = None
    dl3_trend = None
    dl3_next = None

    us1_trend = None
    us1_next = None
    us2_trend = None
    us2_next = None
    us3_trend = None
    us3_next = None

    ul1_trend = None
    ul1_next = None
    ul2_trend = None
    ul2_next = None
    ul3_trend = None
    ul3_next = None

    cs_1 = None
    cs_2 = None
    cs_3 = None
    cs_4 = None
    cs_5 = None
    cs_6 = None
    cs_7 = None
    cs_8 = None
    cs_9 = None
    cs_10 = None
    cs_11 = None
    cs_12 = None
    cs_13 = None
    cs_14 = None
    cs_15 = None
    cs_16 = None
    cs_17 = None
    cs_18 = None
    cs_19 = None
    cs_20 = None
    cs_21 = None
    cs_22 = None
    cs_23 = None
    cs_24 = None
    cs_25 = None
    cs_26 = None
    cs_27 = None
    cs_28 = None
    cs_29 = None
    cs_30 = None
    cs_31 = None

    birthday = None
    gender = None
    gender_1 = None
    education = None
    education_1 = None
    market_year = None
    experience = None
    investment_type = None
    call_back = None

    age = None
    gf = None
    cs_0 = None
    cs = None

    pic_up_trend = None
    pic_down_trend = None

    @staticmethod
    def header():
        heads = ['userId', 'startTime', 'endTime', '省', '市', '赌徒谬误', '认知风格', '认知风格第一部分', '生日', '年龄', '性别CN',
                 '性别', '学历CN', '学历',
                 '入市年份', '经验',
                 '上涨图片', '下跌图片', '认知风格测谎1', '认知风格测谎2']
        return "\t".join(heads)

    def __str__(self):
        return "\t".join(map(str, [self.user_id, self.start_time, self.end_time, self.province, self.city, self.gf
            , self.cs, self.cs_0, self.birthday, self.age, self.gender, self.gender_1, self.education, self.education_1,
                                   self.market_year,
                                   self.experience, self
                             .pic_up_trend, self.pic_down_trend, self.cs_20, self.cs_27]))


def load(path):
    workbook = load_workbook(filename=path)
    sheet = workbook['sheet1']

    results = []
    for row in sheet.iter_rows():
        # row是一个元组，包含行中的所有单元格对象
        # 可以通过索引访问单元格的值，例如 row[0].value 获取第一列的值
        if row[0].value == '作答ID':
            continue
        p = Participant()
        p.user_id = row[1].value
        p.start_time = row[2].value
        p.end_time = row[3].value
        p.province = row[12].value
        p.city = row[13].value

        p.ds1_trend = row[33].value
        p.ds1_next = row[34].value
        p.ds2_trend = row[38].value
        p.ds2_next = row[39].value
        p.ds3_trend = row[43].value
        p.ds3_next = row[44].value

        p.dl1_trend = row[48].value
        p.dl1_next = row[49].value
        p.dl2_trend = row[53].value
        p.dl2_next = row[54].value
        p.dl3_trend = row[58].value
        p.dl3_next = row[59].value

        p.us1_trend = row[63].value
        p.us1_next = row[64].value
        p.us2_trend = row[68].value
        p.us2_next = row[69].value
        p.us3_trend = row[73].value
        p.us3_next = row[74].value

        p.ul1_trend = row[78].value
        p.ul1_next = row[79].value
        p.ul2_trend = row[83].value
        p.ul2_next = row[84].value
        p.ul3_trend = row[88].value
        p.ul3_next = row[89].value

        gf_answer_time = 8000
        if gf_answer_time:
            p.ds1_trend = row[33].value if int(row[35].value) > gf_answer_time else None
            p.ds1_next = row[34].value if int(row[35].value) > gf_answer_time else None

            p.ds2_trend = row[38].value if int(row[40].value) > gf_answer_time else None
            p.ds2_next = row[39].value if int(row[40].value) > gf_answer_time else None

            p.ds3_trend = row[43].value if int(row[45].value) > gf_answer_time else None
            p.ds3_next = row[44].value if int(row[45].value) > gf_answer_time else None

            p.dl1_trend = row[48].value if int(row[50].value) > gf_answer_time else None
            p.dl1_next = row[49].value if int(row[50].value) > gf_answer_time else None
            p.dl2_trend = row[53].value if int(row[55].value) > gf_answer_time else None
            p.dl2_next = row[54].value if int(row[55].value) > gf_answer_time else None
            p.dl3_trend = row[58].value if int(row[60].value) > gf_answer_time else None
            p.dl3_next = row[59].value if int(row[60].value) > gf_answer_time else None

            p.us1_trend = row[63].value if int(row[65].value) > gf_answer_time else None
            p.us1_next = row[64].value if int(row[65].value) > gf_answer_time else None
            p.us2_trend = row[68].value if int(row[70].value) > gf_answer_time else None
            p.us2_next = row[69].value if int(row[70].value) > gf_answer_time else None
            p.us3_trend = row[73].value if int(row[75].value) > gf_answer_time else None
            p.us3_next = row[74].value if int(row[75].value) > gf_answer_time else None

            p.ul1_trend = row[78].value if int(row[80].value) > gf_answer_time else None
            p.ul1_next = row[79].value if int(row[80].value) > gf_answer_time else None
            p.ul2_trend = row[83].value if int(row[85].value) > gf_answer_time else None
            p.ul2_next = row[84].value if int(row[85].value) > gf_answer_time else None
            p.ul3_trend = row[88].value if int(row[90].value) > gf_answer_time else None
            p.ul3_next = row[89].value if int(row[90].value) > gf_answer_time else None

        p.cs_1 = row[95].value
        p.cs_2 = row[99].value
        p.cs_3 = row[103].value
        p.cs_4 = row[107].value
        p.cs_5 = row[111].value
        p.cs_6 = row[115].value
        p.cs_7 = row[119].value
        p.cs_8 = row[123].value
        p.cs_9 = row[127].value
        p.cs_10 = row[131].value
        p.cs_11 = row[135].value
        p.cs_12 = row[139].value
        p.cs_13 = row[143].value
        p.cs_14 = row[147].value
        p.cs_15 = row[151].value
        p.cs_16 = row[155].value
        p.cs_17 = row[159].value
        p.cs_18 = row[163].value
        p.cs_19 = row[167].value
        p.cs_20 = row[171].value  # mixed column
        p.cs_21 = row[175].value
        p.cs_22 = row[179].value
        p.cs_23 = row[183].value
        p.cs_24 = row[187].value
        p.cs_25 = row[191].value
        p.cs_26 = row[195].value
        p.cs_27 = row[199].value  # mixed colum
        p.cs_28 = row[203].value
        p.cs_29 = row[207].value
        p.cs_30 = row[211].value
        p.cs_31 = row[215].value

        answer_time = 9000

        if answer_time:
            p.cs_1 = row[95].value if int(row[96].value) > answer_time else None
            p.cs_2 = row[99].value if int(row[100].value) > answer_time else None
            p.cs_3 = row[103].value if int(row[104].value) > answer_time else None
            p.cs_4 = row[107].value if int(row[108].value) > answer_time else None
            p.cs_5 = row[111].value if int(row[112].value) > answer_time else None
            p.cs_6 = row[115].value if int(row[116].value) > answer_time else None
            p.cs_7 = row[119].value if int(row[120].value) > answer_time else None
            p.cs_8 = row[123].value if int(row[124].value) > answer_time else None
            p.cs_9 = row[127].value if int(row[128].value) > answer_time else None
            p.cs_10 = row[131].value if int(row[132].value) > answer_time else None
            p.cs_11 = row[135].value if int(row[136].value) > answer_time else None
            p.cs_12 = row[139].value if int(row[140].value) > answer_time else None
            p.cs_13 = row[143].value if int(row[144].value) > answer_time else None
            p.cs_14 = row[147].value if int(row[148].value) > answer_time else None
            p.cs_15 = row[151].value if int(row[152].value) > answer_time else None
            p.cs_16 = row[155].value if int(row[156].value) > answer_time else None
            p.cs_17 = row[159].value if int(row[160].value) > answer_time else None
            p.cs_18 = row[163].value if int(row[164].value) > answer_time else None
            p.cs_19 = row[167].value if int(row[168].value) > answer_time else None
            # p.cs_20 = row[171].value if int(row[172].value) > answer_time else None  # mixed column
            p.cs_21 = row[175].value if int(row[176].value) > answer_time else None
            p.cs_22 = row[179].value if int(row[180].value) > answer_time else None
            p.cs_23 = row[183].value if int(row[184].value) > answer_time else None
            p.cs_24 = row[187].value if int(row[188].value) > answer_time else None
            p.cs_25 = row[191].value if int(row[192].value) > answer_time else None
            p.cs_26 = row[195].value if int(row[196].value) > answer_time else None
            # p.cs_27 = row[199].value if int(row[200].value) > answer_time else None  # mixed colum
            p.cs_28 = row[203].value if int(row[204].value) > answer_time else None
            p.cs_29 = row[207].value if int(row[208].value) > answer_time else None
            p.cs_30 = row[211].value if int(row[212].value) > answer_time else None
            p.cs_31 = row[215].value if int(row[216].value) > answer_time else None

        p.birthday = row[217].value
        p.gender = row[219].value
        p.gender_1 = 1 if p.gender == '男' else 0
        p.education = row[221].value
        if p.education == '初中及以下':
            p.education_1 = 0
        elif p.education == '普高/中专/技校/职高':
            p.education_1 = 1
        elif p.education == '专科':
            p.education_1 = 2
        elif p.education == '本科':
            p.education_1 = 3
        elif p.education == '硕士及以上':
            p.education_1 = 4

        p.market_year = row[223].value
        p.experience = row[225].value
        p.investment_type = row[227].value
        p.call_back = row[229].value

        results.append(p)

    return results


def gf_cs(participants):
    for p in participants:
        p.pic_down_trend = sum(
            p == DOWN for p in [p.ds1_trend, p.ds2_trend, p.ds3_trend, p.dl1_trend, p.dl2_trend, p.dl3_trend])
        p.pic_up_trend = sum(
            p == UP for p in [p.us1_trend, p.us2_trend, p.us3_trend, p.ul1_trend, p.ul2_trend, p.ul3_trend])

        gf = sum(g == UP for g in [p.ds1_next, p.ds2_next, p.ds3_next, p.dl1_next, p.dl2_next, p.dl3_next]) + sum(
            g == DOWN for g in [p.us1_next, p.us2_next, p.us2_next, p.us3_next, p.ul1_next, p.ul2_next, p.ul3_next])
        # gf = sum(g == UP for g in [p.ds1_next,  p.dl1_next ]) + sum(
        #     g == DOWN for g in [p.us1_next,  p.ul1_next])
        cs_0 = sum(c == '是' for c in
                   [p.cs_1, p.cs_2, p.cs_3, p.cs_4, p.cs_5, p.cs_6, p.cs_7, p.cs_8, p.cs_9])

        cs = sum(c == '是' for c in
                 [p.cs_10, p.cs_11, p.cs_12, p.cs_13, p.cs_14, p.cs_15, p.cs_16, p.cs_17, p.cs_18, p.cs_19,
                  p.cs_21, p.cs_22, p.cs_23, p.cs_24, p.cs_25, p.cs_26, p.cs_28, p.cs_29, p.cs_30, p.cs_31])
        p.gf = gf
        p.cs = cs
        p.cs_0 = cs_0
        p.age = 2024 - int(p.birthday)


def save_csv(participants, path):
    lines = [Participant.header() + "\r\n"]
    for p in participants:
        if p.cs_20=='是' and p.cs_27=='是':
            continue
        if p.pic_up_trend > 0 and p.pic_down_trend > 0:
            lines.append(str(p) + "\r\n")

    with open(path, 'w') as f:
        f.writelines(lines)


def save(participants, path):
    workbook = Workbook()
    sheet = workbook.active  # ['result']
    heads = ['userId', 'startTime', 'endTime', 'province', 'city', 'gf', 'cs', 'birthday', 'age', 'gender', 'edu',
             'marketYear', 'experience',
             'pic_up_trend', 'pic_down_trend', 'cs2', 'cs3']
    i = 0
    for th in heads:
        sheet.cell(row=1, column=i + 1).value = th
        i = i + 1

    for i in range(len(participants)):
        p = participants[i]
        sheet.cell(row=i + 2, column=1).value = p.user_id
        sheet.cell(row=i + 2, column=2).value = p.start_time
        sheet.cell(row=i + 2, column=3).value = p.end_time
        sheet.cell(row=i + 2, column=4).value = p.province
        sheet.cell(row=i + 2, column=5).value = p.city
        sheet.cell(row=i + 2, column=6).value = p.gf
        sheet.cell(row=i + 2, column=7).value = p.cs
        sheet.cell(row=i + 2, column=8).value = p.birthday
        sheet.cell(row=i + 2, column=9).value = p.age
        sheet.cell(row=i + 2, column=10).value = p.gender
        sheet.cell(row=i + 2, column=11).value = p.education
        sheet.cell(row=i + 2, column=12).value = p.market_year
        sheet.cell(row=i + 2, column=13).value = p.experience
        sheet.cell(row=i + 2, column=14).value = p.pic_up_trend
        sheet.cell(row=i + 2, column=15).value = p.pic_down_trend
        sheet.cell(row=i + 2, column=16).value = p.cs_20
        sheet.cell(row=i + 2, column=17).value = p.cs_27

    workbook.save(path)


if __name__ == '__main__':
    # file_path = 'C:\\Users\\linwy-a\\Downloads\\b598b8888e714d8f9d7db66e527fc677.xlsx'
    file_path = '/Users/linwenyu/Downloads/e0f5c49afd5a4443ab20eb7edf25812b.xlsx'
    participants = load(file_path)
    gf_cs(participants)
    # save(participants, 'results2.xlsx')
    save_csv(participants, '/Users/linwenyu/Downloads/中财课程/投资者行为与心理研究/开题/results.csv')
